package ru.lenwp.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import ru.lenwp.model.Account;
import ru.lenwp.model.Client;
import ru.lenwp.model.User;
import ru.lenwp.util.DbUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Created by Lenar on 02.10.2014.
 */
@Component
public class AccountDao extends AbstractDao<Account> {
    @Autowired
    private UserDao userDao;

    @Override
    public long insert(Account entry) {
        PreparedStatement ps = null;
        Long id = DbUtil.nextId(getTemlate(), "ACCOUNTS_SEQ");

        try {
            ps = getTemlate().execute("INSERT INTO Accounts(user_id, value, blocked, number_account, account_id)" +
                    "VALUES(?,?,?,?,?)", entry.getClient().getId(), entry.getSum(), entry.isBlock() ? "Y" : "N",
                    entry.getNumber(), id);
            initStatementWithParameters(entry, ps, id);
            entry.setId(id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return id;
    }

    public void initStatementWithParameters(Account entry, PreparedStatement ps, Long id) throws SQLException {
        ps.setLong(1, entry.getClient().getId());
        ps.setDouble(2, entry.getSum());
        ps.setString(3, entry.isBlock() ? "Y" : "N");
        ps.setString(4, entry.getNumber());
        ps.setLong(5, id);
        ps.execute();
    }

    @Override
    public void update(Account entry) {
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement("UPDATE accounts SET user_id=?, value=?, blocked=?, number_account=? " +
                    "WHERE account_id=?");
            initStatementWithParameters(entry, pst, entry.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(pst);
        }
    }

    @Override
    public Account findById(long id) {
        Statement st = null;
        ResultSet rs = null;
        Account acc = null;
        try {
            st = conn.createStatement();
            rs = st.executeQuery("SELECT * FROM accounts WHERE account_id=" + id);
            if (!rs.next()) {
                return null;
            }
            acc = getAccount(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(st, rs);
        }
        return acc;
    }

    public Account getAccount(ResultSet rs) throws SQLException {
        Account acc = new Account();
        if (DbUtil.getColumnIndex(rs, "ACCOUNT_ID") > 0) {
            acc.setId(rs.getLong("ACCOUNT_ID"));
        }
        if (DbUtil.getColumnIndex(rs, "VALUE") > 0) {
            acc.setSum(rs.getDouble("VALUE"));
        }
        if (DbUtil.getColumnIndex(rs, "BLOCKED") > 0) {
            acc.setBlock("Y".equals(rs.getString("BLOCKED")));
        }
        if (DbUtil.getColumnIndex(rs, "NUMBER_ACCOUNT") > 0) {
            acc.setNumber(rs.getString("NUMBER_ACCOUNT"));
        }
        if (DbUtil.getColumnIndex(rs, "USER_ID") > 0) {
            Long clientID = rs.getLong("USER_ID");
            User user = userDao.findById(clientID);
            acc.setClient((Client) user);
        }
        return acc;
    }

    @Override
    public void delete(Account entry) {
        Statement st = null;
        try {
            st = conn.createStatement();
            st.execute("DELETE FROM accounts WHERE account_id=" + entry.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(st);
        }
    }

    public List<Account> allAccount() {
        Statement st = null;
        ResultSet rs = null;
        List<Account> res = new ArrayList<Account>();
        try {
            st = conn.createStatement();
            rs = st.executeQuery("SELECT * FROM accounts ORDER BY account_id");
            while (rs.next()) {
                res.add(getAccount(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<Account>();
        } finally {
            DbUtil.close(st, rs);
        }
        return res;
    }

    public List<Account> allAccForUser(User user) {
        Statement st = null;
        ResultSet rs = null;
        List<Account> res = new ArrayList<Account>();
        try {
            st = conn.createStatement();
            rs = st.executeQuery("SELECT * FROM accounts WHERE user_id=" + user.getId() + " ORDER BY account_id");
            while (rs.next()) {
                res.add(getAccount(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<Account>();
        } finally {
            DbUtil.close(st, rs);
        }
        return res;
    }

    public List<Account> showAllAccountsNumber() {
        Statement st = null;
        ResultSet rs = null;
        List<Account> res = new ArrayList<Account>();
        try {
            st = conn.createStatement();
            rs = st.executeQuery("SELECT account_id, value FROM accounts ORDER BY account_id");
            while (rs.next()) {
                res.add(getAccount(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<Account>();
        } finally {
            DbUtil.close(st, rs);
        }
        return res;
    }
}
